﻿using System;
using System.Collections.Generic;
using System.Drawing;

namespace Marmat.Data
{
    public partial class SQLBaseHelper
    {
        public string TableName { get; set; }
        public Suffix Suffix { get; set; }
        public string Where { get; set; }

        public ColumnCollection PrimaryKey
        {
            get
            {
                if (this.m_PrimaryKey is null)
                {
                    this.m_PrimaryKey = new ColumnCollection();
                }

                return this.m_PrimaryKey;
            }
        }

        private ColumnCollection m_PrimaryKey;

        public ColumnCollection Columns
        {
            get
            {
                if (this._Items is null)
                {
                    this._Items = new ColumnCollection();
                }

                return this._Items;
            }
        }

        private ColumnCollection _Items;

        public void Add(string ColumnName, Type DataType, object Value)
        {
            this.Columns.Add(ColumnName, DataType, Value);
        }

        public void Add(string ColumnName, Type DataType, object Value, int MaxLength)
        {
            this.Columns.Add(ColumnName, DataType, Value, MaxLength);
        }

        public string GetSqlString(ModeEnum Mode)
        {
            string Result = null;
            switch (Mode)
            {
                case ModeEnum.Added:
                    {
                        // 声明存储Sql的变量==================================================
                        var ColumnsStr = new System.Text.StringBuilder();
                        var ValuesStr = new System.Text.StringBuilder();
                        // 将需要保存的列，添加到一个集合里面==================================================
                        var Items = new List<Column>();
                        Items.AddRange(this.PrimaryKey);
                        foreach (Column item in this.Columns)
                        {
                            if (this.Columns_Contains(Items, item) == false)
                            {
                                Items.Add(item);
                            }
                        }

                        // 遍历需要保存的列==================================================
                        foreach (Column Column in Items)
                        {
                            // 获取单元格的值==================================================
                            string CellValue = GetSqlValue(Column, this.Suffix);
                            if (CellValue != "Null" && ReferenceEquals(Column.DataType, typeof(string)))
                            {
                                CellValue = "N" + CellValue;
                            }

                            if (CellValue != "Null") // 如果单元格的值不为空的话，则添加到Sql变量里面去
                            {
                                ColumnsStr.Append(",[" + Column.ColumnName + "]");
                                ValuesStr.Append("," + CellValue);
                            }
                        }
                        // 如果Sql变量为空的话，设立初始值
                        if (Items.Count > 0)
                        {
                            if (ColumnsStr.Length == 0 && ValuesStr.Length == 0)
                            {
                                ColumnsStr.Append(",[" + Items[0].ColumnName + "]");
                                ValuesStr.Append(",Null");
                            }
                        }
                        else
                        {
                            break;
                        }

                        ColumnsStr.Remove(0, 1);
                        ValuesStr.Remove(0, 1);
                        if (ColumnsStr.Length > 0 && ValuesStr.Length > 0)
                        {
                            Result = "Insert Into [" + this.TableName + "] (" + ColumnsStr.ToString() + ") Values(" + ValuesStr.ToString() + ")";
                        }

                        break;
                    }

                case ModeEnum.Modified:
                    {
                        var UpdateValues = new System.Text.StringBuilder();
                        foreach (Column Column in this.Columns)
                        {
                            string CellValue = GetSqlValue(Column, this.Suffix);
                            if (CellValue != "Null" && ReferenceEquals(Column.DataType, typeof(string)))
                            {
                                CellValue = "N" + CellValue;
                            }

                            UpdateValues.Append(",[" + Column.ColumnName + "] = " + CellValue);
                        }

                        if (UpdateValues.Length > 0)
                        {
                            UpdateValues.Remove(0, 1);
                            var Filter = new System.Text.StringBuilder();
                            foreach (Column Column in this.PrimaryKey)
                            {
                                string CellValue = GetSqlValue(Column, this.Suffix);
                                if (CellValue != "Null" && ReferenceEquals(Column.DataType, typeof(string)))
                                {
                                    CellValue = "N" + CellValue;
                                }
                                Filter.Append((Filter.Length > 0 ? " And " : "") + $"[{Column.ColumnName}] = {CellValue}");
                            }
                            Result = "UPDATE [" + this.TableName + "] SET " + UpdateValues.ToString() + " WHERE (" + Filter.ToString() + ")";
                        }

                        break;
                    }

                case ModeEnum.Deleted:
                    {
                        var Filter = new System.Text.StringBuilder();
                        foreach (Column Column in this.PrimaryKey)
                        {
                            string CellValue = GetSqlValue(Column, this.Suffix);
                            if (CellValue != "Null" && ReferenceEquals(Column.DataType, typeof(string)))
                            {
                                CellValue = "N" + CellValue;
                            }

                            Filter.Append((Filter.Length > 0 ? " And " : "") + $"[{Column.ColumnName}] = {CellValue}");
                        }

                        if (Filter.Length > 0)
                        {
                            Result = "DELETE FROM [" + this.TableName + "] WHERE (" + Filter.ToString() + ")";
                        }

                        break;
                    }

                case ModeEnum.Where:
                    {
                        var ColumnsStr = new System.Text.StringBuilder();
                        var Filter = new System.Text.StringBuilder();
                        if (this.Columns.Count > 0)
                        {
                            foreach (Column Column in this.Columns)
                            {
                                if (!string.IsNullOrEmpty(Column.ColumnName))
                                {
                                    ColumnsStr.Append(",[" + Column.ColumnName + "]");
                                }
                            }

                            if (ColumnsStr.Length == 0)
                            {
                                break;
                            }

                            ColumnsStr.Remove(0, 1);
                        }
                        else
                        {
                            ColumnsStr.Append("*");
                        }

                        foreach (Column Column in this.PrimaryKey)
                        {
                            string CellValue = GetSqlValue(Column, this.Suffix);
                            if (CellValue != "Null" && ReferenceEquals(Column.DataType, typeof(string)))
                            {
                                CellValue = "N" + CellValue;
                            }

                            Filter.Append(Filter.Length > 0 ? " And " : "" + $"[{ Column.ColumnName}] = {CellValue}");
                        }

                        if (!string.IsNullOrEmpty(this.Where))
                        {
                            if (Filter.Length > 0)
                                Filter.Append(" And ");
                            Filter.Append(this.Where);
                        }
                        Result = $"SELECT {ColumnsStr.ToString()} FROM {this.TableName}" + (Filter.Length > 0 ? " WHERE " : "") + Filter.ToString();
                    }
                    break;
            }
            return Result;
        }

        private bool Columns_Contains(List<Column> Items, Column Item)
        {
            foreach (Column Column in Items)
            {
                if ((Column.ColumnName ?? "") == (Item.ColumnName ?? ""))
                {
                    return true;
                }
            }

            return false;
        }



        /// <summary>
        /// 获取保存时单元格的值(SQL用)
        /// </summary>
        /// <param name="Item"></param>
        /// <param name="Suffix"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        internal static string GetSqlValue(Column Item, Suffix Suffix)
        {
            if (Suffix is null)
            {
                Suffix = new Suffix();
            }

            string Value = null;
            if (!ReferenceEquals(Item.Value, DBNull.Value) && Item.Value is object)
            {
                switch (Item.ValueEnum)
                {
                    case ValueEnum.Parameter:
                        {
                            return Convert.ToString(Item.Value);
                        }

                    case ValueEnum.Value:
                        {
                            switch (Item.DataType.Name ?? "")
                            {
                                case var @case when @case == (typeof(string).Name ?? ""):
                                    {
                                        Value = Item.Value.ToString();
                                        if (Item.MaxLength != -1)
                                        {
                                            Value = Value.Substring(0, Math.Min(Math.Max(Item.MaxLength, 0), Value.Length));
                                        }

                                        if (!string.IsNullOrEmpty(Value))
                                        {
                                            Value = Suffix.String + Value.Replace("'", "''") + Suffix.String;
                                        }
                                        else
                                        {
                                            Value = "Null";
                                        }

                                        break;
                                    }

                                case var case1 when case1 == (typeof(DateTime).Name ?? ""):
                                    {
                                        DateTime val = Convert.ToDateTime(Item.Value);
                                        Value = Suffix.DateTime + val.ToString("yyyy-MM-dd HH:mm:ss") + Suffix.DateTime;
                                        break;
                                    }

                                case var case2 when case2 == (typeof(bool).Name ?? ""):
                                    {
                                        Value = Convert.ToString(Convert.ToBoolean(Item.Value) ? 1 : 0);
                                        break;
                                    }

                                case var case3 when case3 == (typeof(decimal).Name ?? ""):
                                    {
                                        Value = Convert.ToDecimal(Item.Value).ToString();
                                        break;
                                    }

                                case var case4 when case4 == (typeof(double).Name ?? ""):
                                    {
                                        Value = Convert.ToDouble(Item.Value).ToString();
                                        break;
                                    }

                                case var case5 when case5 == (typeof(float).Name ?? ""):
                                    {
                                        Value = Convert.ToSingle(Item.Value).ToString();
                                        break;
                                    }

                                case var case6 when case6 == (typeof(short).Name ?? ""):
                                    {
                                        Value = Convert.ToInt16(Item.Value).ToString();
                                        break;
                                    }

                                case var case7 when case7 == (typeof(int).Name ?? ""):
                                    {
                                        Value = Convert.ToInt32(Item.Value).ToString();
                                        break;
                                    }

                                case var case8 when case8 == (typeof(byte).Name ?? ""):
                                    {
                                        Value = Convert.ToByte(Item.Value).ToString();
                                        break;
                                    }

                                case var case9 when case9 == (typeof(Image).Name ?? ""):
                                    {
                                        var Converter = new ImageConverter();
                                        Item.Value = Converter.ConvertTo(Item.Value, typeof(byte[]));
                                        Value = "0x" + BitConverter.ToString((byte[])Item.Value, 0).Replace("-", "");
                                        break;
                                    }

                                case var case10 when case10 == (typeof(byte[]).Name ?? ""):
                                    {
                                        Value = "0x" + BitConverter.ToString((byte[])Item.Value, 0).Replace("-", "");
                                        break;
                                    }

                                case var case11 when case11 == (typeof(Color).Name ?? ""):
                                    {
                                        if (object.Equals(Item.Value, Color.Empty))
                                        {
                                            var Converter = new ColorConverter();
                                            Value = Converter.ConvertToString(Item.Value);
                                        }
                                        else
                                        {
                                            Value = "Null";
                                        }
                                        break;
                                    }
                            }

                            break;
                        }
                }
            }
            else
            {
                Value = "Null";
            }

            return Value;
        }

        /// <summary>
        /// 获取保存时单元格的值(SQL用)
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        internal static string GetSqlValue(Type DataType, object Obj, int MaxLength, Suffix Suffix)
        {
            if (Suffix is null)
            {
                Suffix = new Suffix();
            }

            string Value = null;
            if (!ReferenceEquals(Obj, DBNull.Value) && Obj is object)
            {
                switch (DataType.Name ?? "")
                {
                    case var @case when @case == (typeof(string).Name ?? ""):
                        {
                            Value = Obj.ToString();
                            if (MaxLength != -1)
                            {
                                Value = Value.Substring(0, Math.Min(Math.Max(MaxLength, 0), Value.Length));
                            }

                            if (!string.IsNullOrEmpty(Value))
                            {
                                Value = Suffix.String + Value.Replace("'", "''") + Suffix.String;
                            }
                            else
                            {
                                Value = "Null";
                            }

                            break;
                        }

                    case var case1 when case1 == (typeof(DateTime).Name ?? ""):
                        {
                            Value = $"{Suffix.DateTime}{Obj}{Suffix.DateTime}";
                            break;
                        }

                    case var case2 when case2 == (typeof(bool).Name ?? ""):
                        {
                            Value = Convert.ToString(Convert.ToBoolean(Obj) ? 1 : 0);
                            break;
                        }

                    case var case3 when case3 == (typeof(decimal).Name ?? ""):
                        {
                            Value = Convert.ToDecimal(Obj).ToString();
                            break;
                        }

                    case var case4 when case4 == (typeof(double).Name ?? ""):
                        {
                            Value = Convert.ToDouble(Obj).ToString();
                            break;
                        }

                    case var case5 when case5 == (typeof(float).Name ?? ""):
                        {
                            Value = Convert.ToSingle(Obj).ToString();
                            break;
                        }

                    case var case6 when case6 == (typeof(short).Name ?? ""):
                        {
                            Value = Convert.ToSingle(Obj).ToString();
                            break;
                        }

                    case var case7 when case7 == (typeof(int).Name ?? ""):
                        {
                            Value = Convert.ToInt32(Obj).ToString();
                            break;
                        }

                    case var case8 when case8 == (typeof(byte).Name ?? ""):
                        {
                            Value = Convert.ToByte(Obj).ToString();
                            break;
                        }

                    case var case9 when case9 == (typeof(Image).Name ?? ""):
                        {
                            var Converter = new ImageConverter();
                            Obj = Converter.ConvertTo(Obj, typeof(byte[]));
                            Value = "0x" + BitConverter.ToString((byte[])Obj, 0).Replace("-", "");
                            break;
                        }

                    case var case10 when case10 == (typeof(byte[]).Name ?? ""):
                        {
                            Value = "0x" + BitConverter.ToString((byte[])Obj, 0).Replace("-", "");
                            break;
                        }

                    case var case11 when case11 == (typeof(Color).Name ?? ""):
                        {
                            if (!object.Equals(Obj, Color.Empty))
                            {
                                var Converter = new ColorConverter();
                                Value = Converter.ConvertToString(Obj);
                            }
                            else
                            {
                                Value = "Null";
                            }

                            break;
                        }
                }
            }
            else
            {
                Value = "Null";
            }

            return Value;
        }

        public enum ModeEnum
        {

            /// <summary>
            /// 新增模式
            /// </summary>
            /// <remarks></remarks>
            Added = 1,

            /// <summary>
            /// 修改模式
            /// </summary>
            /// <remarks></remarks>
            Modified = 2,

            /// <summary>
            /// 删除模式
            /// </summary>
            /// <remarks></remarks>
            Deleted = 4,

            /// <summary>
            /// 查询模式
            /// </summary>
            /// <remarks></remarks>
            Where = 8
        }
    }
}